package saxion.sensorframework.db.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.List;
import org.apache.log4j.Logger;

import saxion.sensorframework.db.conn.*;
import saxion.sensorframework.structuur.DataStruc;
import saxion.sensorframework.structuur.Sensor;

/**
 * The Data Acces Object wich contains all data queries that will be executed by
 * SqlConnect
 * 
 * @author Erik & Martijn
 */
public class DaoSensor {
	static Logger logger = Logger.getLogger(DaoSensor.class.getName());
	/** < logger \sa SensorFramework::logger */
	private SqlConnect conn;

	/**
	 * 
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	public DaoSensor() throws SQLException, InstantiationException,
			IllegalAccessException, ClassNotFoundException {
		conn = SqlConnect.getDbCon();
		this.checkandAddSensorTable();
	}

	/**
	 * Add an sensor object to the database, only used internally
	 * 
	 * @param sensor
	 *            object
	 * @return a Resultset with the generated keys
	 * @throws SQLException
	 */
	private ResultSet addSensor(Sensor sensor) throws SQLException {

		ResultSet rs = null;
		String query = "INSERT INTO sensor (name, description, datatable) values ('"
				+ sensor.getName()
				+ "', '"
				+ sensor.getDescription()
				+ "', '"
				+ sensor.getName() + "')";

		rs = conn.insert(query);
		logger.info("SQL: addSensor() " + query);
		return rs;

	}

	/**
	 * \brief Adds an datatable iterates through the DataStruc \link
	 * saxion.sensorframework.structuur.DataStruc \endlink of the Sensor \link
	 * saxion.sensorframework.structuur.Sensor \endlink
	 * 
	 * This method will add an datatable to the database. It will get the
	 * DataStruc of the Sensor and it will construct a query wich will be
	 * written to the database. The following type's will be supported in this
	 * method with the corresponding database type: -Int -- numeric(18) -String
	 * -- varchar(30) -bool -- Tinyint(1) -datetime -- DATETIME
	 * 
	 * @param sensor
	 * @param sensorNr
	 * @return Tablename
	 * @throws SQLException
	 */
	private String addDataTable(Sensor sensor, int sensorNr)
			throws SQLException {
		// de name van de sensor
		// Omschrijving van de sensor
		// name van de kolom en het type
		StringBuilder sb = new StringBuilder();

		String tableName = sensor.getName() + sensorNr;
		sb.append("create table " + tableName + "(");
		sb.append("ID int NOT NULL UNIQUE KEY AUTO_INCREMENT, ");

		List<DataStruc> list = sensor.getDatastructuur();
		Iterator<DataStruc> it = list.iterator();

		while (it.hasNext()) {
			DataStruc ds = it.next();
			String type = ds.getType();
			switch (type) {
			case "Int":
				type = "numeric(18)";
				break;
			case "String":
				type = "varchar(30)";
				break;
			case "Double":
				type = "float(10,2)";
				break;
			case "int":
				type = "numeric(18)";
				break;
			case "string":
				type = "varchar(30)";
				break;
			case "bool":
				type = "Tinyint(1)";
				break;
			case "boolean":
				type = "Tinyint(1)";
				break;
			case "datetime":
				type = "DATETIME";
			}
			sb.append(ds.getName() + " " + type);
			if (it.hasNext()) { // heeft de iterator een volgend item zoja voeg
								// een , toe
				sb.append(", ");
			}
		}
		sb.append(", ");
		sb.append("`insertedat` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP");
		sb.append(")");

		conn.insert(sb.toString());
		logger.debug(sb.toString());

		return tableName;
	}

	/**
	 * \brief Update the datatable field in the Sensor table.
	 * 
	 * Update the datatable field in the Sensor table. The SensorId is used to
	 * get the right sensor and right dataTablename is also used. This method
	 * will be used after the datatable en sensor are added to the database.
	 * 
	 * @param sensorNr
	 * @param dataTableName
	 * @throws SQLException
	 */
	private void updateDataTableInSensor(int sensorNr, String dataTableName)
			throws SQLException {
		String query = "UPDATE sensor SET datatable='" + dataTableName
				+ "' WHERE ID=" + sensorNr;

		conn.insert(query);
		logger.debug(query);

	}

	/**
	 * \brief Remove sensor used internally
	 * 
	 * Can be used to delete a sensor from the database it will not remove the
	 * Datatable.
	 * 
	 * @param sensornr
	 *            The nr used to delete the sensor
	 * @throws SQLException
	 */
	private void removeSensor(int sensornr) throws SQLException {
		String deletesensorquery = "DELETE FROM sensor WHERE id='" + sensornr
				+ "'";

		conn.insert(deletesensorquery);
		logger.debug(deletesensorquery);

	}

	/**
	 * 
	 * \brief Check and add the Sensor table if its not present in the database
	 * 
	 * Used to check if the Sensor table exists if not the query will add it to
	 * the database.
	 * 
	 * @throws SQLException
	 */
	private void checkandAddSensorTable() throws SQLException {
		StringBuilder sb = new StringBuilder();
		sb.append("CREATE TABLE IF NOT EXISTS `sensor` (");
		sb.append("`ID` int(11) NOT NULL AUTO_INCREMENT,");
		sb.append("`NAME` varchar(45) DEFAULT NULL,");
		sb.append("`DESCRIPTION` varchar(255) DEFAULT NULL,");
		sb.append("`DATATABLE` varchar(45) DEFAULT NULL,");
		sb.append("PRIMARY KEY (`ID`),");
		sb.append("UNIQUE KEY `NAME` (`NAME`,`DESCRIPTION`)");
		sb.append(") AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;");

		conn.insert(sb.toString());
		logger.debug(sb.toString());

	}

	/**
	 * \brief Add sensor data to the database
	 * 
	 * Add Sensor data to the corresponding datatable of the sensor with the id
	 * given to this method. It will read the list wich contains the data and
	 * builds a query using a StringBuilder.
	 * 
	 * @param sensorNr
	 * @param list
	 * @throws SQLException
	 */
	public void addSensorData(int sensorNr, List<Object> list)
			throws SQLException {
		StringBuilder sb = new StringBuilder();

		String datatableName = this.getDataTableName(sensorNr);

		sb.append("INSERT INTO " + datatableName + " VALUES( null,");

		Iterator<Object> it = list.iterator();

		while (it.hasNext()) {
			Object obj = it.next();

			if (obj.toString().equals("false") || obj.toString().equals("true")) {
				sb.append(obj.toString());
			} else {
				sb.append("'" + obj.toString() + "'");
			}

			sb.append(",");

		}

		sb.append(" null)");

		logger.debug("SQL: addSensorData() " + sb.toString());

		conn.insert(sb.toString());

	}

	/**
	 * \brief Add a sensor and its Datatable
	 * 
	 * Add a Sensor and a Datatable based on the DataStruc of the Sensor. This
	 * method will call method's in this class in the the following steps
	 * -addSensor Add the sensor to the database -getSensornr Get the generated
	 * Id -addDataTable Add a datatable to the database -updateDataTableInSensor
	 * Update the name of the datatable in the corresponding Sensor row
	 * 
	 * @param sensor
	 * @return Sensor nr
	 * @throws SQLException
	 */
	public int addSensorAndDataTable(Sensor sensor) throws SQLException {
		this.addSensor(sensor);
		int sensorNr = this.getSensorNr(sensor.getName());
		this.addDataTable(sensor, sensorNr);
		this.updateDataTableInSensor(sensorNr, sensor.getName() + sensorNr);

		return sensorNr;
	}

	/**
	 * Get a specific sensor based on its name
	 * 
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getSensor(String name) throws SQLException {
		ResultSet rs = null;
		String query = "SELECT * FROM sensor WHERE name = '" + name + "'";

		rs = conn.query(query);
		logger.debug(query);

		return rs;
	}

	/**
	 * Get a specific sensor nr
	 * 
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	public int getSensorNr(String name) throws SQLException {
		ResultSet rs = null;
		String query = "SELECT id FROM sensor WHERE name = '" + name + "'";
		int nr = 0;

		rs = conn.query(query);
		logger.debug(query);
		rs.next();
		nr = Integer.parseInt(rs.getString(1));

		return nr;
	}

	/**
	 * Get all sensors
	 * 
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getAllSensors() throws SQLException {
		ResultSet rs = null;
		String query = "SELECT * FROM sensor";

		rs = conn.query(query);
		logger.debug(query);

		return rs;
	}

	/**
	 * Gets the data columns of a Sensor
	 * 
	 * @param sensorNr
	 * @return ResultSet with the Columns
	 * @throws SQLException
	 */
	public ResultSet getSensorDataTypes(int sensorNr) throws SQLException {
		String datatable = this.getDataTableName(sensorNr);

		String query = "SHOW COLUMNS FROM " + datatable;

		ResultSet rs = null;

		rs = conn.query(query);
		logger.debug(query);

		return rs;
	}

	/**
	 * Returns all the data of a specific sensor
	 * 
	 * @param sensorNr
	 * @return Resultset with all the data
	 * @throws SQLException
	 */
	public ResultSet getSensorData(int sensorNr) throws SQLException {
		String dataTableName = this.getDataTableName(sensorNr);
		String query = "SELECT * FROM " + dataTableName;

		ResultSet rs = null;

		rs = conn.query(query);
		logger.debug(query);

		return rs;
	}

	/**
	 * Get specific data of a sensor between two Timestamp's
	 * 
	 * @param minimum
	 *            Start data
	 * @param maximum
	 *            End data
	 * @param sensorNr
	 *            The sensor nr
	 * @return A ResultSet with the data
	 * @throws SQLException
	 */
	public ResultSet getSpecificSensorData(long minimum, long maximum,
			int sensorNr) throws SQLException {
		String dataTableName = this.getDataTableName(sensorNr);

		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String strminimum = df.format(minimum);
		String strmaximum = df.format(maximum);
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT * FROM " + dataTableName);
		sb.append(" WHERE insertedat BETWEEN ");
		sb.append("'" + strminimum + "' and '" + strmaximum + "'");

		ResultSet rs = null;

		rs = conn.query(sb.toString());
		logger.debug(sb.toString());

		return rs;
	}

	/**
	 * Get the datatable name
	 * 
	 * @param sensorNr
	 * @return String met de desbetreffende
	 * @throws SQLException
	 */
	public String getDataTableName(int sensorNr) throws SQLException {
		String selectdatatablequery = "SELECT datatable FROM sensor WHERE ID ='"
				+ sensorNr + "'";
		String datatablename = "";

		ResultSet rs = conn.query(selectdatatablequery);
		logger.debug(selectdatatablequery);
		rs.next();
		datatablename = rs.getString(1);

		return datatablename;
	}

	/**
	 * Remove sensor and the Datatable
	 * 
	 * @param sensorNr
	 * @throws SQLException
	 */
	public void removeSensorAndDataTable(int sensorNr) throws SQLException {
		String datatablename = this.getDataTableName(sensorNr);
		this.removeSensor(sensorNr);
		String deletedatatablequery = "DROP TABLE " + datatablename;

		conn.insert(deletedatatablequery);
		logger.debug(deletedatatablequery);

	}
}
